#include "dataform.h"
#include "ui_dataform.h"
#include "classdialog.h"
#include "itemdialog.h"
#include <QFile>
#include <QMessageBox>
#include <QDebug>
#include <QSize>
#include <QAction>
#include <QDate>
#include <QVariant>
#include <cstdio>

DataForm::DataForm(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::DataForm)
{
    ui->setupUi(this);

    this->ui->data_table->setGeometry(0, 60, this->width(), this->height() - 60);

    this->db = QSqlDatabase::addDatabase("QSQLITE", "data");
    this->db.setDatabaseName("data.db");
    if (!this->db.open()) {
        QMessageBox::critical(this, "系统错误", "无法打开数据库");
        exit(1);
    }

    this->date_id = 0;
    this->is_init = false;
    this->show_class_id = 0;
    this->refresh();
}

DataForm::~DataForm()
{
    if (this->db.isOpen()) {
        this->db.close();
    }
    QSqlDatabase::removeDatabase("data");
    delete ui;
}

void DataForm::resizeEvent(QResizeEvent *event) {
    QSize size = event->size();
    this->ui->data_table->setGeometry(0, 60, size.width(), size.height() - 60);
}

void DataForm::on_add_class_clicked()
{
    ClassDialog cd(this);
    cd.exec();
    this->refresh();
}

void DataForm::refresh() {
    this->db = QSqlDatabase::addDatabase("QSQLITE", "data");
    this->db.setDatabaseName("data.db");
    if (!this->db.open()) {
        QMessageBox::critical(this, "系统错误", "无法打开数据库");
        return;
    }

    QDate date = QDate::currentDate();
    int month = date.month();
    month = 12;
    QSqlQuery item_q = this->db.exec("SELECT `id`, `name` FROM `item` ORDER BY `id` ASC");
    this->item_id_list.clear();
    QStringList item_name_list;
    item_name_list.append("组别");
    item_name_list.append("班级");
    if (this->date_id == 0) {
        while (item_q.next()) {
            this->item_id_list.append(item_q.value("id").toInt());
            item_name_list.append(item_q.value("name").toString());
        }
        item_name_list.append("排名");
    } else if (this->date_id == 1) {
        while (item_q.next()) {
            this->item_id_list.append(item_q.value("id").toInt());
        }
        item_name_list.append("第1周得分");
        item_name_list.append("第2周得分");
        item_name_list.append("第3周得分");
        item_name_list.append("第4周得分");
        item_name_list.append("第5周得分");
        item_name_list.append("本月平均分");
        item_name_list.append("排名");
    } else if (this->date_id == 2) {
        while (item_q.next()) {
            this->item_id_list.append(item_q.value("id").toInt());
        }
        // 上学期2月-7月15日左右，下学期9月1日-2月左右
        if (month >= 9 || month == 1 || month == 2) {  // 下
            item_name_list.append("9月份得分");
            item_name_list.append("9月份排名");
            item_name_list.append("10月份得分");
            item_name_list.append("10月份排名");
            item_name_list.append("11月份得分");
            item_name_list.append("11月份排名");
            item_name_list.append("12月份得分");
            item_name_list.append("12月份排名");
            item_name_list.append("1月份得分");
            item_name_list.append("1月份排名");
        } else {  // 上
            item_name_list.append("2月份得分");
            item_name_list.append("2月份排名");
            item_name_list.append("3月份得分");
            item_name_list.append("3月份排名");
            item_name_list.append("4月份得分");
            item_name_list.append("4月份排名");
            item_name_list.append("5月份得分");
            item_name_list.append("5月份排名");
            item_name_list.append("6月份得分");
            item_name_list.append("6月份排名");
            item_name_list.append("7月份得分");
            item_name_list.append("7月份排名");
        }
    }
    if (this->date_id > 0) {
        item_name_list.append("排名汇总");
    }

    QSqlQuery class_q = this->db.exec(
                "SELECT `c`.`id`, `c`.`name`, `c`.`group_name`"
                " FROM `class` AS `c`"
                " ORDER BY `c`.`id` ASC");
    this->ui->data_table->clear();
    this->class_id_list.clear();
    this->ui->data_table->setColumnCount(item_name_list.length());
    this->ui->data_table->setHorizontalHeaderLabels(item_name_list);
    int row = 0;
    while (class_q.next()) {
        this->class_id_list.append(class_q.value("id").toInt());
        this->ui->data_table->setRowCount(row + 1);
        this->ui->data_table->setItem(
                    row, 0, new QTableWidgetItem(class_q.value("group_name").toString()));
        this->ui->data_table->setItem(
                    row, 1, new QTableWidgetItem(class_q.value("name").toString()));
        ++row;
    }

    this->ui->date_combobox->setCurrentIndex(this->date_id);

    QDateTime time;
    QString sql;
    switch (this->date_id) {
    case 0:
    {
        this->ui->plus_score_btn->setEnabled(true);
        this->ui->minus_score_btn->setEnabled(true);
        this->ui->op_score_spinbox->setEnabled(true);
        date = date.addDays(1 - date.dayOfWeek());
        time.setDate(date);
        qint64 datetime = time.toSecsSinceEpoch();
        row = 0;
        this->score_list.clear();
        Score score;
        for (int c_id : qAsConst(this->class_id_list)) {
            if (c_id == 0) {
                continue;
            }
            int column_index = 0;
            QVector<ScoreItem> score_item_list;
            ScoreItem score_item;
            int sum = 0;
            for (int i_id : qAsConst(this->item_id_list)) {
                sql = "SELECT `id`, `init_score` FROM `item` WHERE `id` = " + QVariant(i_id).toString();
                QSqlQuery score_q = this->db.exec(sql);
                if (score_q.next()) {
                    sql = "SELECT SUM(`op_score`) AS `op_score` FROM `score`";
                    sql += " WHERE `item_id` = " + QVariant(i_id).toString();
                    sql += " AND `class_id` = " + QVariant(c_id).toString();
                    sql += " AND `date_time` >= " + QVariant(datetime).toString();
                    QSqlQuery sum_q = this->db.exec(sql);
                    if (sum_q.first()) {
                        int init_score = score_q.value("init_score").toInt();
                        int op_score = sum_q.value("op_score").toInt();
                        int result_score = init_score + op_score;
                        sum += result_score;
                        score_item.item_id = i_id;
                        score_item.score = result_score;
                        score_item_list.append(score_item);
                        if (column_index % this->item_id_list.length() == 0) {
                            column_index = 0;
                        }
                        QTableWidgetItem *item = new QTableWidgetItem(QVariant(result_score).toString());
                        this->ui->data_table->setItem(row, column_index + 2, item);
                    }
                }
                ++column_index;
            }
            score.row = row;
            score.sum = sum;
            score.items = new ScoreItem[score_item_list.length()];
            for (int i = 0; i < score_item_list.length(); ++i) {
                score.items[i] = score_item_list.at(i);
            }
            this->score_list.append(score);
            ++row;
        }
        int score_list_len = this->score_list.length();
        Score *score_array = new Score[score_list_len];
        for (int i = 0; i < score_list_len; ++i) {
            score_array[i] = this->score_list.at(i);
        }
        for (int i = 0; i < score_list_len - 1; ++i) {
            for (int j = 0; j < score_list_len - i - 1; ++j) {
                if (score_array[j].sum < score_array[j + 1].sum) {
                    qSwap(score_array[j], score_array[j + 1]);
                }
            }
        }
        for (int i = 0; i < score_list_len; ++i) {
            score_array[i].sort = i + 1;
        }
        for (int i = 0; i < score_list_len - 1; ++i) {
            for (int j = 0; j < score_list_len - i - 1; ++j) {
                if (score_array[j].row > score_array[j + 1].row) {
                    qSwap(score_array[j], score_array[j + 1]);
                }
            }
        }

        if (score_list_len == 0) {
            for (int i = 1; i < this->class_id_list.length(); ++i) {
                QTableWidgetItem *item = new QTableWidgetItem(QVariant(0).toString());
                this->ui->data_table->setItem(i - 1, 2, item);
            }
        } else {
            int item_list_len = this->item_id_list.length();
            for (int i = 0; i < score_list_len; ++i) {
                int sort = score_array[i].sort;
                row = score_array[i].row;
                QTableWidgetItem *item = new QTableWidgetItem(QVariant(sort).toString());
                this->ui->data_table->setItem(row, item_list_len + 2, item);
            }
        }
        for (int i = 0; i < score_list_len; ++i) {
            delete[] score_array[i].items;
        }
        delete[] score_array;
        break;
    }
    case 1:
    {
        this->ui->plus_score_btn->setEnabled(false);
        this->ui->minus_score_btn->setEnabled(false);
        this->ui->op_score_spinbox->setEnabled(false);
        date = date.addDays(1 - date.daysInMonth());
        time.setDate(date);
        qint64 datetime1 = time.toSecsSinceEpoch();
        date = date.addDays(7 - date.dayOfWeek());
        time.setDate(date);
        qint64 datetime2 = time.toSecsSinceEpoch();
        date = date.addDays(7);
        time.setDate(date);
        qint64 datetime3 = time.toSecsSinceEpoch();
        date = date.addDays(7);
        time.setDate(date);
        qint64 datetime4 = time.toSecsSinceEpoch();
        date.setDate(date.year(), date.month() + 1, 1);
        date = date.addDays(-1);
        time.setDate(date);
        qint64 datetime5 = time.toSecsSinceEpoch();
        row = 0;
        QVector<WeekScore> week_score_list;
        QVector<WeekScoreItem> item_list;
        WeekScore week_score;
        week_score.items = nullptr;
        for (int c_id : qAsConst(this->class_id_list)) {
            int result_score = 0;
            item_list.clear();
            WeekScoreItem item;
            for (int i = 0; i < 5; ++i) {
                item.score[i] = 0;
            }
            for (int i = 0; i < this->item_id_list.length(); ++i) {
                for (int j = 0; j < 5; ++j) {
                    item.score[j] = 0;
                }
                sql = "SELECT `id` FROM `item`";
                QSqlQuery score_q = this->db.exec(sql);
                QString item_id_str = "";
                while (score_q.next()) {
                    if (item_id_str == "") {
                        item_id_str = "(" + score_q.value("id").toString();
                    } else {
                        item_id_str += "," + score_q.value("id").toString();
                    }
                }
                item_id_str += ")";
                sql = "SELECT `init_score` FROM `item`";
                score_q = this->db.exec(sql);
                while (score_q.next()) {
                    int init_score = score_q.value("init_score").toInt();
                    for (int i = 0; i < 5; ++i) {
                        item.score[i] += init_score;
                    }
                    result_score += init_score;
                }
                QString sql1 = "";
                sql = "SELECT SUM(`op_score`) AS `op_score` FROM `score`";
                sql += " WHERE `item_id` in " + item_id_str;
                sql += " AND `class_id` = " + QVariant(c_id).toString();

                QSqlQuery sum_q[5];
                sql1 = " AND `date_time` >= " + QVariant(datetime1).toString();
                sql1 += " AND `date_time` < " + QVariant(datetime2).toString();
                sum_q[0] = this->db.exec(sql + sql1);
                sql1 = " AND `date_time` >= " + QVariant(datetime2).toString();
                sql1 += " AND `date_time` < " + QVariant(datetime3).toString();
                sum_q[1] = this->db.exec(sql + sql1);
                sql1 = " AND `date_time` >= " + QVariant(datetime3).toString();
                sql1 += " AND `date_time` < " + QVariant(datetime4).toString();
                sum_q[2] = this->db.exec(sql + sql1);
                sql1 = " AND `date_time` >= " + QVariant(datetime4).toString();
                sql1 += " AND `date_time` < " + QVariant(datetime5).toString();
                sum_q[3] = this->db.exec(sql + sql1);
                sql1 = " AND `date_time` >= " + QVariant(datetime5).toString();
                sum_q[4] = this->db.exec(sql + sql1);
                for (int j = 0; j < 5; ++j) {
                    if (sum_q[j].first()) {
                        int op_score = sum_q[j].value("op_score").toInt();
                        item.score[j] += op_score;
                        result_score += op_score;
                    }
                }
                item_list.append(item);
            }
            int item_list_len = item_list.length();
            if (item_list_len > 0) {
                int sum = 0;
                for (int j = 0; j < 5; ++j) {
                    sum += item.score[j];
                }
                week_score.row = row;
                week_score.average = (float)sum / 5.0f;
                week_score.items = new WeekScoreItem[item_list_len];
                for (int j = 0; j < item_list_len; ++j) {
                    week_score.items[j] = item_list.at(j);
                }
                week_score_list.append(week_score);
            } else {
                week_score.average = 0.0;
                week_score.items = nullptr;
            }
            ++row;
        }
        int week_score_list_len = week_score_list.length();
        WeekScore *score_array = new WeekScore[week_score_list_len];
        for (int i = 0; i < week_score_list_len; ++i) {
            score_array[i] = week_score_list.at(i);
        }

        int score_list_len = week_score_list_len;
        for (int i = 0; i < score_list_len - 1; ++i) {
            for (int j = 0; j < score_list_len - i - 1; ++j) {
                if (score_array[j].average < score_array[j + 1].average) {
                    qSwap(score_array[j], score_array[j + 1]);
                }
            }
        }
        for (int i = 0; i < score_list_len; ++i) {
            score_array[i].month_sort = i + 1;
        }

        for (int sort_i = 0; sort_i < 5; ++sort_i) {
            for (int i = 0; i < score_list_len - 1; ++i) {
                for (int j = 0; j < score_list_len - i - 1; ++j) {
                    if (score_array[j].items[j].score[sort_i] < score_array[j + 1].items[j].score[sort_i]) {
                        qSwap(score_array[j], score_array[j + 1]);
                    }
                }
            }
            for (int i = 0; i < score_list_len; ++i) {
                score_array[i].sort[sort_i] = i + 1;
            }
        }

        for (int i = 0; i < score_list_len - 1; ++i) {
            for (int j = 0; j < score_list_len - i - 1; ++j) {
                if (score_array[j].row > score_array[j + 1].row) {
                    qSwap(score_array[j], score_array[j + 1]);
                }
            }
        }

        if (score_list_len == 0) {
            for (int i = 0; i < this->class_id_list.length(); ++i) {
                QTableWidgetItem *item[5];
                for (int j = 0; j < 5; ++j) {
                    item[j] = new QTableWidgetItem("0");
                    this->ui->data_table->setItem(i, j + 2, item[j]);
                }
                QTableWidgetItem *item7 = new QTableWidgetItem(QVariant(score_array[i - 1].average).toString());
                QTableWidgetItem *item8 = new QTableWidgetItem("0");
                this->ui->data_table->setItem(i, 7, item7);
                this->ui->data_table->setItem(i, 8, item8);
            }
        } else {
            int item_list_len = item_list.length();
            for (int i = 0; i < score_list_len; ++i) {
                QString month_sort_str = QVariant(score_array[i].month_sort).toString();
                for (int k = 0; k < 5; ++k) {
                    for (int j = 0; j < item_list_len; ++j) {
                        QTableWidgetItem *item = new QTableWidgetItem(QVariant(score_array[i].items[j].score[k]).toString());
                        this->ui->data_table->setItem(i, k + 2, item);
                    }
                }
                char score_chars[10];
                sprintf(score_chars, "%.2f", score_array[i].average);
                QTableWidgetItem *item7 = new QTableWidgetItem(QVariant(score_chars).toString());
                QTableWidgetItem *item8 = new QTableWidgetItem(month_sort_str);
                this->ui->data_table->setItem(i, 7, item7);
                this->ui->data_table->setItem(i, 8, item8);
                QString str9 = "";
                QVector<int> sort_list;
                QVector<int> times_list;
                for (int j = 0; j < 5; ++j) {
                    int sort = score_array[i].sort[j];
                    int sort_index = sort_list.indexOf(sort);
                    if (sort_index == -1) {
                        sort_list.append(sort);
                        times_list.append(1);
                    } else {
                        ++times_list[sort_index];
                    }
                }
                int times_len = times_list.length();
                for (int j = 0; j < times_len; ++j) {
                    str9 += "第" + QVariant(sort_list.at(j)).toString() + "名";
                    str9 += QVariant(times_list.at(j)).toString() + "次";
                }
                QTableWidgetItem *item9 = new QTableWidgetItem(str9);
                this->ui->data_table->setItem(i, 9, item9);
            }
        }

        for (int i = 0; i < week_score_list_len; ++i) {
            delete[] score_array[i].items;
        }
        delete[] score_array;
        break;
    }
    case 2:
    {
        this->ui->plus_score_btn->setEnabled(false);
        this->ui->minus_score_btn->setEnabled(false);
        this->ui->op_score_spinbox->setEnabled(false);
        // 上学期2月-7月15日左右，下学期9月1日-2月左右
        if (month >= 9 || month == 1 || month == 2) {  // 下
            int year = 0;
            if (month == 1 || month == 2) {
                year = date.year() - 1;
            } else {
                year = date.year();
            }
            /**
            item_name_list.append("9月份得分");
            item_name_list.append("9月份排名");
            item_name_list.append("10月份得分");
            item_name_list.append("10月份排名");
            item_name_list.append("11月份得分");
            item_name_list.append("11月份排名");
            item_name_list.append("12月份得分");
            item_name_list.append("12月份排名");
            item_name_list.append("1月份得分");
            item_name_list.append("1月份排名");
             */
            date.setDate(year, 9, 1);
            time.setDate(date);
            qint64 datetime1 = time.toSecsSinceEpoch();
            date.setDate(year, 10, 1);
            time.setDate(date);
            qint64 datetime2 = time.toSecsSinceEpoch();
            date.setDate(year, 11, 1);
            time.setDate(date);
            qint64 datetime3 = time.toSecsSinceEpoch();
            date.setDate(year, 12, 1);
            time.setDate(date);
            qint64 datetime4 = time.toSecsSinceEpoch();
            date.setDate(year + 1, 1, 1);
            time.setDate(date);
            qint64 datetime5 = time.toSecsSinceEpoch();
            date.setDate(year + 1, 2, 1);
            time.setDate(date);
            qint64 datetime6 = time.toSecsSinceEpoch();
            row = 0;
            QVector<TermScore> term_score_list;
            QVector<TermScoreItem> item_list;
            TermScore term_score;
            term_score.items = nullptr;
            for (int c_id : qAsConst(this->class_id_list)) {
                item_list.clear();
                TermScoreItem item;
                item.score = 0;
                item.sort = 0;
                for (int i = 0; i < this->item_id_list.length(); ++i) {
                    item.score = 0;
                    item.sort = 0;
                    sql = "SELECT `id` FROM `item`";
                    QSqlQuery score_q = this->db.exec(sql);
                    QString item_id_str = "";
                    while (score_q.next()) {
                        if (item_id_str == "") {
                            item_id_str = "(" + score_q.value("id").toString();
                        } else {
                            item_id_str += "," + score_q.value("id").toString();
                        }
                    }
                    item_id_str += ")";
                    sql = "SELECT `init_score` FROM `item`";
                    score_q = this->db.exec(sql);
                    while (score_q.next()) {
                        int init_score = score_q.value("init_score").toInt();
                        item.score += init_score;
                    }
                    QString sql1 = "";
                    sql = "SELECT SUM(`op_score`) AS `op_score` FROM `score`";
                    sql += " WHERE `item_id` in " + item_id_str;
                    sql += " AND `class_id` = " + QVariant(c_id).toString();

                    QSqlQuery sum_q[5];
                    sql1 = " AND `date_time` >= " + QVariant(datetime1).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime2).toString();
                    sum_q[0] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime2).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime3).toString();
                    sum_q[1] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime3).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime4).toString();
                    sum_q[2] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime4).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime5).toString();
                    sum_q[3] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime5).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime6).toString();
                    sum_q[4] = this->db.exec(sql + sql1);
                    for (int j = 0; j < 5; ++j) {
                        if (sum_q[j].first()) {
                            int op_score = sum_q[j].value("op_score").toInt();
                            item.score += op_score;
                        }
                    }
                    item_list.append(item);
                }
                int item_list_len = item_list.length();
                if (item_list_len > 0) {
                    int sum = 0;
                    for (int j = 0; j < 5; ++j) {
                        sum += item.score;
                    }
                    term_score.row = row;
                    term_score.items = new TermScoreItem[item_list_len];
                    for (int i = 0; i < item_list_len; ++i) {
                        term_score.items[i] = item_list.at(i);
                    }
                    term_score_list.append(term_score);
                } else {
                    term_score.items = nullptr;
                }
                ++row;
            }
            int term_score_list_len = term_score_list.length();
            TermScore *score_array = new TermScore[term_score_list_len];
            for (int i = 0; i < term_score_list_len; ++i) {
                score_array[i] = term_score_list.at(i);
            }

            int score_list_len = term_score_list_len;
            for (int k = 0; k < item_list.length(); ++k) {
                for (int i = 0; i < score_list_len - 1; ++i) {
                    for (int j = 0; j < score_list_len - i - 1; ++j) {
                        if (score_array[j].items[k].score < score_array[j + 1].items[k].score) {
                            qSwap(score_array[j], score_array[j + 1]);
                        }
                    }
                }
                for (int i = 0; i < score_list_len; ++i) {
                    score_array[i].items[k].sort = i + 1;
                }
            }

            for (int i = 0; i < score_list_len - 1; ++i) {
                for (int j = 0; j < score_list_len - i - 1; ++j) {
                    if (score_array[j].row > score_array[j + 1].row) {
                        qSwap(score_array[j], score_array[j + 1]);
                    }
                }
            }

            if (score_list_len == 0) {
                for (int i = 0; i < this->class_id_list.length(); ++i) {
                    QTableWidgetItem *item_score[5];
                    QTableWidgetItem *item_sort[5];
                    for (int j = 0, k = 2; j < 5; ++j, k += 2) {
                        item_score[j] = new QTableWidgetItem("0");
                        item_sort[j] = new QTableWidgetItem("0");
                        this->ui->data_table->setItem(i, k, item_score[j]);
                        this->ui->data_table->setItem(i, k + 1, item_sort[j]);
                    }
                }
            } else {
                int item_list_len = item_list.length();
                for (int i = 0; i < score_list_len; ++i) {
                    for (int j = 0, k = 2; j < 5; ++j, k += 2) {
                        for (int l = 0; l < item_list_len; ++l) {
                            QString score_str = QVariant(score_array[i].items[l].score).toString();
                            QString sort_str = QVariant(score_array[i].items[l].sort).toString();
                            QTableWidgetItem *score = new QTableWidgetItem(score_str);
                            QTableWidgetItem *sort = new QTableWidgetItem(sort_str);
                            this->ui->data_table->setItem(i, k, score);
                            this->ui->data_table->setItem(i, k + 1, sort);
                        }
                    }
                    QString str12 = "";
                    QVector<int> sort_list;
                    QVector<int> times_list;
                    for (int j = 0; j < 5; ++j) {
                        for (int k = 0; k < item_list_len / this->item_id_list.length(); ++k) {
                            int sort = score_array[i].items[k].sort;
                            int sort_index = sort_list.indexOf(sort);
                            if (sort_index == -1) {
                                sort_list.append(sort);
                                times_list.append(1);
                            } else {
                                ++times_list[sort_index];
                            }
                        }
                    }
                    int times_len = times_list.length();
                    for (int j = 0; j < times_len; ++j) {
                        str12 += "第" + QVariant(sort_list.at(j)).toString() + "名";
                        str12 += QVariant(times_list.at(j)).toString() + "次";
                    }
                    QTableWidgetItem *item9 = new QTableWidgetItem(str12);
                    this->ui->data_table->setItem(i, 12, item9);
                }
            }

            for (int i = 0; i < score_list_len; ++i) {
                delete[] score_array[i].items;
            }
            delete[] score_array;
        } else {  // 上
            int year = date.year();
            /**
            item_name_list.append("2月份得分");
            item_name_list.append("2月份排名");
            item_name_list.append("3月份得分");
            item_name_list.append("3月份排名");
            item_name_list.append("4月份得分");
            item_name_list.append("4月份排名");
            item_name_list.append("5月份得分");
            item_name_list.append("5月份排名");
            item_name_list.append("6月份得分");
            item_name_list.append("6月份排名");
            item_name_list.append("7月份得分");
            item_name_list.append("7月份排名");
             */
            date.setDate(year, 2, 1);
            time.setDate(date);
            qint64 datetime1 = time.toSecsSinceEpoch();
            date.setDate(year, 3, 1);
            time.setDate(date);
            qint64 datetime2 = time.toSecsSinceEpoch();
            date.setDate(year, 4, 1);
            time.setDate(date);
            qint64 datetime3 = time.toSecsSinceEpoch();
            date.setDate(year, 5, 1);
            time.setDate(date);
            qint64 datetime4 = time.toSecsSinceEpoch();
            date.setDate(year, 6, 1);
            time.setDate(date);
            qint64 datetime5 = time.toSecsSinceEpoch();
            date.setDate(year, 7, 1);
            time.setDate(date);
            qint64 datetime6 = time.toSecsSinceEpoch();
            date.setDate(year, 8, 1);
            time.setDate(date);
            qint64 datetime7 = time.toSecsSinceEpoch();
            row = 0;
            QVector<TermScore> term_score_list;
            QVector<TermScoreItem> item_list;
            TermScore term_score;
            term_score.items = nullptr;
            for (int c_id : qAsConst(this->class_id_list)) {
                item_list.clear();
                TermScoreItem item;
                item.score = 0;
                item.sort = 0;
                for (int i = 0; i < this->item_id_list.length(); ++i) {
                    item.score = 0;
                    item.sort = 0;
                    sql = "SELECT `id` FROM `item`";
                    QSqlQuery score_q = this->db.exec(sql);
                    QString item_id_str = "";
                    while (score_q.next()) {
                        if (item_id_str == "") {
                            item_id_str = "(" + score_q.value("id").toString();
                        } else {
                            item_id_str += "," + score_q.value("id").toString();
                        }
                    }
                    item_id_str += ")";
                    sql = "SELECT `init_score` FROM `item`";
                    score_q = this->db.exec(sql);
                    while (score_q.next()) {
                        int init_score = score_q.value("init_score").toInt();
                        item.score += init_score;
                    }
                    QString sql1 = "";
                    sql = "SELECT SUM(`op_score`) AS `op_score` FROM `score`";
                    sql += " WHERE `item_id` in " + item_id_str;
                    sql += " AND `class_id` = " + QVariant(c_id).toString();

                    QSqlQuery sum_q[6];
                    sql1 = " AND `date_time` >= " + QVariant(datetime1).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime2).toString();
                    sum_q[0] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime2).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime3).toString();
                    sum_q[1] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime3).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime4).toString();
                    sum_q[2] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime4).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime5).toString();
                    sum_q[3] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime5).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime6).toString();
                    sum_q[4] = this->db.exec(sql + sql1);
                    sql1 = " AND `date_time` >= " + QVariant(datetime6).toString();
                    sql1 += " AND `date_time` < " + QVariant(datetime7).toString();
                    sum_q[5] = this->db.exec(sql + sql1);
                    for (int j = 0; j < 6; ++j) {
                        if (sum_q[j].first()) {
                            int op_score = sum_q[j].value("op_score").toInt();
                            item.score += op_score;
                            item_list.append(item);
                        }
                    }
                }
                int item_list_len = item_list.length();
                if (item_list_len > 0) {
                    term_score.row = row;
                    term_score.items = new TermScoreItem[item_list_len];
                    for (int i = 0; i < item_list_len; ++i) {
                        term_score.items[i] = item_list.at(i);
                    }
                    term_score_list.append(term_score);
                } else {
                    term_score.items = nullptr;
                }
                ++row;
            }
            int term_score_list_len = term_score_list.length();
            TermScore *score_array = new TermScore[term_score_list_len];
            for (int i = 0; i < term_score_list_len; ++i) {
                score_array[i] = term_score_list.at(i);
            }

            int score_list_len = term_score_list_len;
            for (int k = 0; k < item_list.length(); ++k) {
                for (int i = 0; i < score_list_len - 1; ++i) {
                    for (int j = 0; j < score_list_len - i - 1; ++j) {
                        if (score_array[j].items[k].score < score_array[j + 1].items[k].score) {
                            qSwap(score_array[j], score_array[j + 1]);
                        }
                    }
                }
                for (int i = 0; i < score_list_len; ++i) {
                    score_array[i].items[k].sort = i + 1;
                }
            }

            for (int i = 0; i < score_list_len - 1; ++i) {
                for (int j = 0; j < score_list_len - i - 1; ++j) {
                    if (score_array[j].row > score_array[j + 1].row) {
                        qSwap(score_array[j], score_array[j + 1]);
                    }
                }
            }

            if (score_list_len == 0) {
                for (int i = 0; i < this->class_id_list.length(); ++i) {
                    QTableWidgetItem *item_score[6];
                    QTableWidgetItem *item_sort[6];
                    for (int j = 0, k = 2; j < 6; ++j, k += 2) {
                        item_score[j] = new QTableWidgetItem("0");
                        item_sort[j] = new QTableWidgetItem("0");
                        this->ui->data_table->setItem(i, k, item_score[j]);
                        this->ui->data_table->setItem(i, k + 1, item_sort[j]);
                    }
                }
            } else {
                int item_list_len = item_list.length();
                for (int i = 0; i < score_list_len; ++i) {
                    for (int j = 0, k = 2; j < 6; ++j, k += 2) {
                        for (int l = 0; l < item_list_len; ++l) {
                            QString score_str = QVariant(score_array[i].items[l].score).toString();
                            QString sort_str = QVariant(score_array[i].items[l].sort).toString();
                            QTableWidgetItem *score = new QTableWidgetItem(score_str);
                            QTableWidgetItem *sort = new QTableWidgetItem(sort_str);
                            this->ui->data_table->setItem(i, k, score);
                            this->ui->data_table->setItem(i, k + 1, sort);
                        }
                    }
                    QString str14 = "";
                    QVector<int> sort_list;
                    QVector<int> times_list;
                    for (int k = 0; k < item_list_len / this->item_id_list.length(); ++k) {
                        int sort = score_array[i].items[k].sort;
                        int sort_index = sort_list.indexOf(sort);
                        if (sort_index == -1) {
                            sort_list.append(sort);
                            times_list.append(1);
                        } else {
                            ++times_list[sort_index];
                        }
                    }
                    int times_len = times_list.length();
                    for (int j = 0; j < times_len; ++j) {
                        str14 += "第" + QVariant(sort_list.at(j)).toString() + "名";
                        str14 += QVariant(times_list.at(j)).toString() + "次";
                    }
                    QTableWidgetItem *item14 = new QTableWidgetItem(str14);
                    this->ui->data_table->setItem(i, 14, item14);
                }
            }

            for (int i = 0; i < score_list_len; ++i) {
                delete[] score_array[i].items;
            }
            delete[] score_array;
        }
        break;
    }
    }

    this->is_init = true;
    this->db.close();
    QSqlDatabase::removeDatabase("data");
}


void DataForm::on_add_item_clicked()
{
    ItemDialog item_dlg(this);
    item_dlg.exec();
    this->refresh();
}


void DataForm::on_date_combobox_activated(int index)
{
    this->date_id = index;
    this->refresh();
}


void DataForm::on_refresh_btn_clicked()
{
    this->refresh();
}


void DataForm::on_plus_score_btn_clicked()
{
    int row = this->ui->data_table->currentRow();
    int column = this->ui->data_table->currentColumn();
    if (column < 2 || column > this->item_id_list.length() + 1) {
        QMessageBox::critical(this, "系统错误", "无法修改该数值");
        return;
    }

    this->db = QSqlDatabase::addDatabase("QSQLITE", "data");
    this->db.setDatabaseName("data.db");
    if (!this->db.open()) {
        QMessageBox::critical(this, "系统错误", "无法打开数据库");
        return;
    }
    QString c_id_str = QVariant(this->class_id_list.at(row)).toString();
    QString item_id_str = QVariant(column - 1).toString();
    QString time_str = QVariant(QDateTime::currentSecsSinceEpoch()).toString();
    QString score = this->ui->op_score_spinbox->text();
    QString sql = "INSERT INTO `score`(`class_id`, `item_id`, `op_score`, `date_time`) VALUES (";
    sql += c_id_str + ", " + item_id_str + ", " + score + ", " + time_str + ")";
    this->db.exec(sql);
    QSqlError err = this->db.lastError();
    if (err.isValid()) {
        QMessageBox::critical(this, "系统错误", "数值修改失败，" + err.databaseText());
    }

    this->db.close();
    QSqlDatabase::removeDatabase("data");
    this->refresh();
}


void DataForm::on_minus_score_btn_clicked()
{
    int row = this->ui->data_table->currentRow();
    int column = this->ui->data_table->currentColumn();
    if (column < 2 || column > this->item_id_list.length() + 1) {
        QMessageBox::critical(this, "系统错误", "无法修改该数值");
        return;
    }

    this->db = QSqlDatabase::addDatabase("QSQLITE", "data");
    this->db.setDatabaseName("data.db");
    if (!this->db.open()) {
        QMessageBox::critical(this, "系统错误", "无法打开数据库");
        return;
    }
    QString c_id_str = QVariant(this->class_id_list.at(row)).toString();
    QString item_id_str = QVariant(column - 1).toString();
    QString time_str = QVariant(QDateTime::currentSecsSinceEpoch()).toString();
    QString score = this->ui->op_score_spinbox->text();
    QString sql = "INSERT INTO `score`(`class_id`, `item_id`, `op_score`, `date_time`) VALUES (";
    sql += c_id_str + ", " + item_id_str + ", -" + score + ", " + time_str + ")";
    this->db.exec(sql);
    QSqlError err = this->db.lastError();
    if (err.isValid()) {
        QMessageBox::critical(this, "系统错误", "数值修改失败，" + err.databaseText());
    }

    this->db.close();
    QSqlDatabase::removeDatabase("data");
    this->refresh();
}

